#include "projectdatabaseoperations.h"
#include <QDebug>
#include "tabledefine.h"

QSqlDatabase projectDB;

bool connectProjectDB(QString dbName)
{
    if (QSqlDatabase::contains(PROJECTDB_CONNECTION))
    {
        projectDB = QSqlDatabase::database(PROJECTDB_CONNECTION);
    }
    else
    {
        projectDB = QSqlDatabase::addDatabase("QSQLITE", PROJECTDB_CONNECTION);
        projectDB.setDatabaseName(dbName);
        projectDB.setUserName("yanshang");
        projectDB.setPassword("912");
    }
    if(!projectDB.open())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<projectDB.lastError();
        return false;
    }
    return true;
}

bool initProjectDB()
{   
    QSqlQuery query(projectDB);
    QStringList tables = projectDB.tables();
    
    //建筑表
    QString sql = QString("create table %1 (%2 INTEGER primary key, "
                  "%3 VARCHAR(200), "
                  "%4 VARCHAR(500),"
                  "%5 int, "
                  "%6 int, "
                  "%7 int)").arg(BUILDING_TABLE_NAME, BUILDING_ID,
                                 NAME, 
                                 DESCRIBE,
                                 NUM,
                                 UP_ID,
                                 TYPE);
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<sql;
    if(!tables.contains(BUILDING_TABLE_NAME, Qt::CaseInsensitive))
    {
        if(!query.exec(sql))
        {
            qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"create error"<<projectDB.lastError();
            return false;
        }
        else
            qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"建筑表创建成功";
    }
    else
        qWarning()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"建筑表格已存在";
    
    //设备表
    sql.clear();
    sql = QString("create table %1 (%2 INTEGER primary key, "
                  "%3 int default0, "
                  "%4 int default0, "
                  "%5 VARCHAR(100), "
                  "%6 VARCHAR(10), "
                  "%7 VARCHAR(100), "
                  "%8 int, "
                  "%9 int, "
                  "%10 int, "
                  "%11 int, "
                  "%12 int, "
                  "%13 VARCHAR(50), "
                  "%14 VARCHAR(50), "
                  "%15 VARCHAR(200), "
                  "%16 VARCHAR(20), "
                  "%17 VARCHAR(20), "
                  "%18 VARCHAR(20), "
                  "%19 VARCHAR(20),"
                  "%20 VARCHAR(20),"
                  "%21 VARCHAR(200))").arg(DEVICE_TABLE_NAME).arg(DEVICE_ID)
                                        .arg(PRODUCT_ID)
                                        .arg(BUILDING_ID)
                                        .arg(NAME)
                                        .arg(DEVICE_PA)
                                        .arg(APP_PROGRAM)
                                        .arg(ADR)
                                        .arg(PRG)
                                        .arg(PAR)
                                        .arg(GRP)
                                        .arg(CFG)
                                        .arg(MANUFACTURERS)
                                        .arg(ORDER_NUM)
                                        .arg(PRODUCT)
                                        .arg(UNIT_TYPE)
                                        .arg(MANUFACTURERSDATA)
                                        .arg(VERSION)
                                        .arg(DEVICEDB_NAME)
                                        .arg(DEVICE_SN)
                                        .arg(DESCRIBE);
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<sql;
    if(!tables.contains(DEVICE_TABLE_NAME, Qt::CaseInsensitive))
    {
        if(!query.exec(sql))
        {
            qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"create error"<<projectDB.lastError();
            return false;
        }
        else
            qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"设备表创建完成";
    }
    else
        qWarning()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"设备表格已存在";
    
    //接口对象表
    sql.clear();
    sql = QString("create table %1 (%2 INTEGER primary key autoincrement, "
                  "%3 int, "
                  "%4 int default0, "
                  "%5 int default0,"
                  "%6 int, "
                  "%7 int, "
                  "%8 VARCHAR(100), "
                  "%9 int, "
                  "%10 int, "
                  "%11 int, "
                  "%12 VARCHAR(200), "
                  "%13 VARCHAR(20), "
                  "%14 VARCHAR(500), "
                  "%15 int default-1, "
                  "%16 int default-1)").arg(INTERFACE_OBJECT_TABLE_NAME).arg(ID)
                                        .arg(INTERFACE_OBJECT_ID)
                                        .arg(PRODUCT_ID)
                                        .arg(DEVICE_ID)
                                        .arg(CHANNEL_NUM)
                                        .arg(CHANNEL_ID)
                                        .arg(CHANNEL_NAME)
                                        .arg(CHANNEL_TYPE)
                                        .arg(INTERFACE_OBJECT_NUM)
                                        .arg(EEPROM_ADDRESS)
                                        .arg(INTERFACE_OBJECT_DESCRIBE)
                                        .arg(VALUE_SET)
                                        .arg(VALUE_MEANING)
                                        .arg(DEFAULT_VALUE)
                                        .arg(VALUE);
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<sql;
    if(!tables.contains(INTERFACE_OBJECT_TABLE_NAME, Qt::CaseInsensitive))
    {
        if(!query.exec(sql))
        {
            qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"create error"<<projectDB.lastError();
            return false;
        }
        else
            qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"接口对象表创建完成";
    }
    else
        qWarning()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"接口对象表已存在";
    
    //通讯对象表
    sql.clear();
    sql = QString("create table %1 (%2 INTEGER primary key autoincrement, "
                  "%3 int, "
                  "%4 int default0, "
                  "%5 int default0, "
                  "%6 int, "
                  "%7 int, "
                  "%8 VARCHAR(200), "
                  "%9 BOOLEAN default0, "
                  "%10 BOOLEAN default0, "
                  "%11 BOOLEAN default0, "
                  "%12 BOOLEAN default0, "
                  "%13 BOOLEAN default0, "
                  "%14 BOOLEAN default0,"
                  "%15 int,"
                  "%16 int, "
                  "%17 VARCHAR(1000),"
                  "%18 VARCHAR(200))").arg(COMM_OBJECT_TABLE_NAME).arg(ID)
                                        .arg(COMM_OBJECT_ID)
                                        .arg(PRODUCT_ID)
                                        .arg(DEVICE_ID)
                                        .arg(EEPROM_ADDRESS)
                                        .arg(COMM_OBJECT_NUM)
                                        .arg(FUNCTION)
                                        .arg(C_BIT)
                                        .arg(R_BIT)
                                        .arg(W_BIT)
                                        .arg(V_BIT)
                                        .arg(T_BIT)
                                        .arg(U_BIT)
                                        .arg(DATA_TYPE)
                                        .arg(PRIORITY)
                                        .arg(RELATION_GA)
                                        .arg(DESCRIBE);
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<sql;
    if(!tables.contains(COMM_OBJECT_TABLE_NAME, Qt::CaseInsensitive))
    {
        if(!query.exec(sql))
        {
            qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"create error"<<projectDB.lastError();
            return false;
        }
        else
            qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"通讯对象表创建完成";
    }
    else
        qWarning()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"通讯对象表已存在";
    
    //规则表
    sql.clear();
    sql = QString("create table %1 (%2 INTEGER primary key autoincrement, "
                  "%3 int"
                  "%4 int default0, "
                  "%5 int default0, "
                  "%6 int, "
                  "%7 int, "
                  "%8 int, "
                  "%9 int, "
                  "%10 BOOLEAN default1, "
                  "%11 BOOLEAN default1, "
                  "%12 BOOLEAN default1, "
                  "%13 BOOLEAN default1, "
                  "%14 BOOLEAN default1, "
                  "%15 BOOLEAN default1)").arg(INTERFACE_COMM_OBJECT_RULE_TABLE_NAME).arg(ID)
                                        .arg(RULE_ID)
                                        .arg(PRODUCT_ID)
                                        .arg(DEVICE_ID)
                                        .arg(INTERFACE_OBJECT_NUM)
                                        .arg(COMM_OBJECT_NUM)
                                        .arg(RULE_TYPE)
                                        .arg(INTERFACE_OBJECT_VALUE)
                                        .arg(C_BIT)
                                        .arg(R_BIT)
                                        .arg(W_BIT)
                                        .arg(V_BIT)
                                        .arg(T_BIT)
                                        .arg(U_BIT);
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<sql;
    if(!tables.contains(COMM_OBJECT_TABLE_NAME, Qt::CaseInsensitive))
    {
        if(!query.exec(sql))
        {
            qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"create error"<<projectDB.lastError();
            return false;
        }
        else
            qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"规则表创建完成";
    }
    else
        qWarning()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"规则表已存在";
    
    //群组地址表
    sql.clear();
    sql = QString("create table %1 (%2 INTEGER primary key autoincrement, "
                  "%3 int, "
                  "%4 int, "
                  "%5 VARCHAR(200), "
                  "%6 VARCHAR(200), "
                  "%7 int, "
                  "%8 VARCHAR(10), "
                  "%9 int default0,"
                  "%10 int default0, "
                  "%11 int,"
                  "%12 int,"
                  "%13 int)").arg(ADDRESS_TABLE_NAME).arg(ID)
                                    .arg(ADDRESS_ID)
                                    .arg(VALUE)
                                    .arg(NAME)
                                    .arg(DESCRIBE)
                                    .arg(UP_ID)
                                    .arg(ADDRESS)
                                    .arg(PRODUCT_ID)
                                    .arg(DEVICE_ID)
                                    .arg(EEPROM_ADDRESS)
                                    .arg(ADDRESS_NUM)
                                    .arg(TYPE);
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<sql;
    if(!tables.contains(ADDRESS_TABLE_NAME, Qt::CaseInsensitive))
    {
        if(!query.exec(sql))
        {
            qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"create error"<<projectDB.lastError();
            return false;
        }
        else
            qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"群组地址表创建完成";
    }
    else
        qWarning()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"群组地址表已存在";
    
    //关联表
    sql.clear();
    sql = QString("create table %1 (%2 INTEGER primary key autoincrement, "
                  "%3 int, "
                  "%4 int default0, "
                  "%5 int default0, "
                  "%6 int, "
                  "%7 int, "
                  "%8 int,"
                  "%9 int)").arg(RELATION_TABLE_NAME, ID, 
                                 RELATION_ID,
                                 PRODUCT_ID,
                                 DEVICE_ID,
                                 EEPROM_ADDRESS,
                                 RELATION_NUM,
                                 COMM_OBJECT_NUM,
                                 ADDRESS_NUM);
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<sql;
    if(!tables.contains(RELATION_TABLE_NAME, Qt::CaseInsensitive))
    {
        if(!query.exec(sql))
        {
            qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"create error"<<projectDB.lastError();
            return false;
        }
        else
            qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"关联表创建成功";
    }
    else
        qWarning()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"关联表已存在";
    
    return true;
}

bool insertBuilding(int buildingID, QString buildingName, QString buildingDescribe, int num,
                      int upID, int type)
{
    QSqlQuery query(projectDB);
    QString sql = QString("select %1 from %2").arg(NAME, BUILDING_TABLE_NAME);
    if(!query.exec(sql))
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    while(query.next())
    {
        if(query.value(0).toString() == buildingName)
        {
            qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"建筑名称已存在"<<query.value(0).toString();
            QString tmp = QString("(%1)").arg(buildingID - 1);
            buildingName.append(tmp);
        }
    }
    
    if(-1 == num)
        sql = QString("insert into %1 (%2, %3, %4, %5, %6) values (?, ?, ?, ?, ?)").arg(BUILDING_TABLE_NAME, 
                                                                          BUILDING_ID, NAME, DESCRIBE, UP_ID, TYPE);
    else
        sql = QString("insert into %1 values (?, ?, ?, ?, ?, ?)").arg(BUILDING_TABLE_NAME,
                                                                      BUILDING_ID, NAME, DESCRIBE, NUM, UP_ID, TYPE);
    
    if(!query.prepare(sql))
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    query.addBindValue(buildingID);
    query.addBindValue(buildingName);
    query.addBindValue(buildingDescribe);
    if(-1 != num)
        query.addBindValue(num);
    query.addBindValue(upID);
    query.addBindValue(type);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"插入建筑成功";
    return true;
}

bool insertDevice(int deviceID, int productID, int buildingID, QString name, QString PA, QString appProgram, int adr, int prg, 
               int par, int grp, int cfg, QString manufacturers, QString orderNum, 
               QString product, QString unitType, QString manufacturesData, QString version, QString dbName, QString SN, QString describe)
{
    QSqlQuery query(projectDB);
    QString sql = QString("insert into %1 values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)").arg(DEVICE_TABLE_NAME);
    
    if(!query.prepare(sql))
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    query.addBindValue(deviceID);
    query.addBindValue(productID);
    query.addBindValue(buildingID);
    query.addBindValue(name);
    query.addBindValue(PA);
    query.addBindValue(appProgram);
    query.addBindValue(adr);
    query.addBindValue(prg);
    query.addBindValue(par);
    query.addBindValue(grp);
    query.addBindValue(cfg);
    query.addBindValue(manufacturers);
    query.addBindValue(orderNum);
    query.addBindValue(product);
    query.addBindValue(unitType);
    query.addBindValue(manufacturesData);
    query.addBindValue(version);
    query.addBindValue(dbName);
    query.addBindValue(SN);
    query.addBindValue(describe);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"插入设备成功";
    return true;
}

bool insertAddress(int addressID, int value, QString name, QString describe, int upID, 
                QString address, int productID, int deviceID, int eepromAddress, int addressNum,int type)
{
    QSqlQuery query(projectDB);
    QString sql = QString("insert into %1 (%2,%3,%4,%5,%6,%7,%8,%9,%10,%11,%12) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
            .arg(ADDRESS_TABLE_NAME)
            .arg(ADDRESS_ID)
            .arg(VALUE)
            .arg(NAME)
            .arg(DESCRIBE)
            .arg(UP_ID)
            .arg(ADDRESS)
            .arg(PRODUCT_ID)
            .arg(DEVICE_ID)
            .arg(EEPROM_ADDRESS)
            .arg(ADDRESS_NUM)
            .arg(TYPE);
    
    if(!query.prepare(sql))
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    query.addBindValue(addressID);
    query.addBindValue(value);
    query.addBindValue(name);
    query.addBindValue(describe);
    query.addBindValue(upID);
    query.addBindValue(address);
    query.addBindValue(productID);
    query.addBindValue(deviceID);
    query.addBindValue(eepromAddress);
    query.addBindValue(addressNum);
    query.addBindValue(type);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"插入地址成功";
    return true;
}

bool insertRelation(int relationID, int productID, int deviceID, int relationEepromAddress, 
                    int relationNum, int commObjectNum, int addressNum)
{
    QSqlQuery query(projectDB);
    QString sql = QString("insert into %1 (%2,%3,%4,%5,%6,%7,%8) values (?, ?, ?, ?, ?, ?, ?)")
            .arg(RELATION_TABLE_NAME)
            .arg(RELATION_ID)
            .arg(PRODUCT_ID)
            .arg(DEVICE_ID)
            .arg(EEPROM_ADDRESS)
            .arg(RELATION_NUM)
            .arg(COMM_OBJECT_NUM)
            .arg(ADDRESS_NUM);
    
    if(!query.prepare(sql))
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    query.addBindValue(relationID);
    query.addBindValue(productID);
    query.addBindValue(deviceID);
    query.addBindValue(relationEepromAddress);
    query.addBindValue(relationNum);
    query.addBindValue(commObjectNum);
    query.addBindValue(addressNum);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"插入关联信息成功";
    return true;
}

bool deleteBuildingByID(int buildingID)
{
    QSqlQuery query(projectDB);
    QString sql = QString("delete from %1 where %2 = ?").arg(BUILDING_TABLE_NAME, BUILDING_ID);
    
    query.prepare(sql);
    query.addBindValue(buildingID);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"删除成功";
    return true;
}

bool deleteBuildingByName(QString name)
{
    QSqlQuery query(projectDB);
    QString sql = QString("delete from %1 where %2 = ?").arg(BUILDING_TABLE_NAME, NAME);
    
    query.prepare(sql);
    query.addBindValue(name);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"删除成功";
    return true;
}

bool deleteDeviceByID(int deviceID)
{
    QSqlQuery query(projectDB);
    QString sql = QString("delete from %1 where %2 = ?").arg(DEVICE_TABLE_NAME, DEVICE_ID);
    
    query.prepare(sql);
    query.addBindValue(deviceID);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"删除成功";
    return true;
}

bool deleteAllDeviceDataByDeviceID(int deviceID)
{
    //删除接口对象表对应设备的数据
    QSqlQuery query(projectDB);
    QString sql = QString("delete from %1 where %2 = ?").arg(INTERFACE_OBJECT_TABLE_NAME, DEVICE_ID);
    query.prepare(sql);
    query.addBindValue(deviceID);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"删除成功";
    
    //删除通讯对象表~
    sql.clear();
    sql = QString("delete from %1 where %2 = ?").arg(COMM_OBJECT_TABLE_NAME, DEVICE_ID);
    query.prepare(sql);
    query.addBindValue(deviceID);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"删除成功";
    
    //删除群组地址表~
    sql.clear();
    sql = QString("delete from %1 where %2 = ?").arg(ADDRESS_TABLE_NAME, DEVICE_ID);
    query.prepare(sql);
    query.addBindValue(deviceID);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"删除成功";
    
    //删除关联表~
    sql.clear();
    sql = QString("delete from %1 where %2 = ?").arg(RELATION_TABLE_NAME, DEVICE_ID);
    query.prepare(sql);
    query.addBindValue(deviceID);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"删除成功";
    
    //获取设备名然后在建筑表删除该设备的信息
    QString deviceName = selectDeviceNameByID(deviceID);
    if(deviceName.isNull())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"获取设备名失败，将无法删除建筑表中的设备信息";
        return false;
    }
    sql.clear();
    sql = QString("delete from %1 where %2 = ?").arg(BUILDING_TABLE_NAME, NAME);
    query.prepare(sql);
    query.addBindValue(deviceName);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"删除成功";
    
    //删除设备表~
    sql.clear();
    sql = QString("delete from %1 where %2 = ?").arg(DEVICE_TABLE_NAME, DEVICE_ID);
    query.prepare(sql);
    query.addBindValue(deviceID);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"删除成功";
    
    return true;
}

bool deleteAddressByID(int addressID)
{
    QSqlQuery query(projectDB);
    QString sql = QString("delete from %1 where %2 = ?").arg(ADDRESS_TABLE_NAME, ADDRESS_ID);
    
    query.prepare(sql);
    query.addBindValue(addressID);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"删除成功";
    return true;
}

bool deleteAddressByAddressAndType(QString address, int type)
{
    QSqlQuery query(projectDB);
    QString sql = QString("delete from %1 where %2 = ? and %3 = ?").arg(ADDRESS_TABLE_NAME, ADDRESS, TYPE);
    
    query.prepare(sql);
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"sql";
    query.addBindValue(address);
    query.addBindValue(type);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"删除成功";
    return true;
}

bool deleteAddressByDeviceIDAndGAAndType(int deviceID, QString GA, int type)
{
    QSqlQuery query(projectDB);
    QString sql = QString("delete from %1 where %2 = ? and %3 = ? and %4 = ?").arg(ADDRESS_TABLE_NAME, DEVICE_ID, ADDRESS, TYPE);
    
    query.prepare(sql);
    query.addBindValue(deviceID);
    query.addBindValue(GA);
    query.addBindValue(type);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"删除成功";
    return true;
}

bool deleteRelationByDeviceIDAndCommObjectNumAndAddressNum(int deviceID, int relationID)
{
    QSqlQuery query(projectDB);
    QString sql = QString("delete from %1 where %2 = ? and %3 = ?").arg(RELATION_TABLE_NAME, DEVICE_ID, RELATION_ID);
    
    query.prepare(sql);
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"sql:"<<sql;
    query.addBindValue(deviceID);
    query.addBindValue(relationID);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"删除成功";
    return true;
}

bool updateBuildingName(int buildingID, QString buildingName)
{
    QSqlQuery query(projectDB);
    QString sql = QString("update %1 set %2 = %3 where %4 = %5").arg(BUILDING_TABLE_NAME, 
                                                                     NAME, _NAME, 
                                                                     BUILDING_ID, _BUILDING_ID);
    
    query.prepare(sql);
    query.bindValue(_NAME, buildingName);
    query.bindValue(_BUILDING_ID, buildingID);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"更新建筑名称成功";
    return true;
}

bool updateBuildingDescribe(int buildingID, QString describe)
{
    QSqlQuery query(projectDB);
    QString sql = QString("update %1 set %2 = %3 where %4 = %5").arg(BUILDING_TABLE_NAME, 
                                                                     DESCRIBE, _DESCRIBE, 
                                                                     BUILDING_ID, _BUILDING_ID);
    
    query.prepare(sql);
    query.bindValue(_DESCRIBE, describe);
    query.bindValue(_BUILDING_ID, buildingID);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"更新建筑描述成功";
    return true;
}

bool updateBuildingNum(int buildingID, int num)
{
    QSqlQuery query(projectDB);
    QString sql = QString("update %1 set %2 = %3 where %4 = %5").arg(BUILDING_TABLE_NAME, 
                                                                     NUM, _NUM, 
                                                                     BUILDING_ID, _BUILDING_ID);
    
    query.prepare(sql);
    query.bindValue(_NUM, num);
    query.bindValue(_BUILDING_ID, buildingID);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"更新建筑序号成功";
    return true;
}

bool updateDeviceName(int deviceID, QString deviceName)
{
    QSqlQuery query(projectDB);
    QString sql = QString("update %1 set %2 = %3 where %4 = %5").arg(DEVICE_TABLE_NAME, 
                                                                     NAME, _NAME, 
                                                                     DEVICE_ID, _DEVICE_ID);
    
    query.prepare(sql);
    query.bindValue(_NAME, deviceName);
    query.bindValue(_DEVICE_ID, deviceID);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"更新设备名称成功";
    return true;
}

bool updateDevicePA(int deviceID, QString devicePA)
{
    QSqlQuery query(projectDB);
    QString sql = QString("update %1 set %2 = %3 where %4 = %5").arg(DEVICE_TABLE_NAME, 
                                                                     DEVICE_PA, _DEVICE_PA, 
                                                                     DEVICE_ID, _DEVICE_ID);
    
    query.prepare(sql);
    query.bindValue(_DEVICE_PA, devicePA);
    query.bindValue(_DEVICE_ID, deviceID);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"更新设备物理地址成功";
    return true;
}

bool updateDeviceDescribe(int deviceID, QString describe)
{
    QSqlQuery query(projectDB);
    QString sql = QString("update %1 set %2 = %3 where %4 = %5").arg(DEVICE_TABLE_NAME, 
                                                                     DESCRIBE, _DESCRIBE, 
                                                                     DEVICE_ID, _DEVICE_ID);
    
    query.prepare(sql);
    query.bindValue(_DESCRIBE, describe);
    query.bindValue(_DEVICE_ID, deviceID);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"更新设备描述成功";
    return true;
}

bool updateAddressName(int id, QString addressName)
{
    QSqlQuery query(projectDB);
    QString sql = QString("update %1 set %2 = %3 where %4 = %5").arg(ADDRESS_TABLE_NAME, 
                                                                     NAME, _NAME, 
                                                                     ID, _ID);
    
    query.prepare(sql);
    query.bindValue(_NAME, addressName);
    query.bindValue(_ID, id);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"更新地址名称成功";
    return true;
}

bool updateAddressDescribe(int id, QString describe)
{
    QSqlQuery query(projectDB);
    QString sql = QString("update %1 set %2 = %3 where %4 = %5").arg(ADDRESS_TABLE_NAME, 
                                                                     DESCRIBE, _DESCRIBE, 
                                                                     ID, _ID);
    
    query.prepare(sql);
    query.bindValue(_DESCRIBE, describe);
    query.bindValue(_ID, id);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"更新地址描述成功";
    return true;
}

bool updateAddressIDAndAddressNumAndEepromAddressToOrder(int deviceID, int addressID)
{
    QSqlQuery query(projectDB);
    QString sql = QString("update %1 set %2 = %2 - 1,%3 = %3 - 1,%4 = %4 - 2 where %5 = %6 and %2 > %7")
            .arg(ADDRESS_TABLE_NAME).arg(ADDRESS_ID).arg(ADDRESS_NUM)
            .arg(EEPROM_ADDRESS).arg(DEVICE_ID).arg(_DEVICE_ID).arg(_ADDRESS_ID);
    
    query.prepare(sql);
    query.bindValue(_DEVICE_ID, deviceID);
    query.bindValue(_ADDRESS_ID, addressID);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"更新成功";
    return true;
}

bool updateRelationIDAndEepromAddressByDeviceIDAndRelationID(int deviceID, int relationID)
{
    QSqlQuery query(projectDB);
    QString sql = QString("update %1 set %2 = %2 - 1, %3 = %3 - 1, %4 = %4 - 1 where %5 = %6 and %2 > %7")
            .arg(RELATION_TABLE_NAME).arg(RELATION_ID)
            .arg(RELATION_NUM).arg(EEPROM_ADDRESS)
            .arg(DEVICE_ID).arg(_DEVICE_ID).arg(_RELATION_ID);
    
    query.prepare(sql);
    query.bindValue(_DEVICE_ID, deviceID);
    query.bindValue(_RELATION_ID, relationID);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"更新成功";
    return true;
}

bool updateRelationDescribeByDeviceIDAndObjectNum(int deviceID, int commObjectNum, QString describe)
{
    QSqlQuery query(projectDB);
    QString sql = QString("update %1 set %2 = %3 where %4 = %5 and %6 = %7").arg(COMM_OBJECT_TABLE_NAME, 
                                                                                 DESCRIBE, _DESCRIBE, 
                                                                                 DEVICE_ID, _DEVICE_ID,
                                                                                 COMM_OBJECT_NUM, _COMM_OBJECT_NUM);
    
    query.prepare(sql);
    query.bindValue(_DESCRIBE, describe);
    query.bindValue(_DEVICE_ID, deviceID);
    query.bindValue(_COMM_OBJECT_NUM, commObjectNum);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"更新成功";
    return true;
}

bool updateRelationGAByDeviceIDAndCommObjectNum(int deviceID, int commObjectNum, QString relationGA)
{
    QSqlQuery query(projectDB);
    QString sql = QString("update %1 set %2 = %3 where %4 = %5 and %6 = %7").arg(COMM_OBJECT_TABLE_NAME, 
                                                                                 RELATION_GA, _RELATION_GA, 
                                                                                 DEVICE_ID, _DEVICE_ID,
                                                                                 COMM_OBJECT_NUM, _COMM_OBJECT_NUM);
    
    query.prepare(sql);
    query.bindValue(_RELATION_GA, relationGA);
    query.bindValue(_DEVICE_ID, deviceID);
    query.bindValue(_COMM_OBJECT_NUM, commObjectNum);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"更新成功";
    return true;
}

bool updateRelationGAAndDescribeNullByCommObjectNumAndDeviceID(int deviceID, int commObjectNum)
{
    QSqlQuery query(projectDB);
    QString sql = QString("update %1 set %2 = null,%3 = null where %4 = %5 and %6 = %7").arg(COMM_OBJECT_TABLE_NAME, 
                                                                                 RELATION_GA, DESCRIBE,
                                                                                 DEVICE_ID, _DEVICE_ID,
                                                                                 COMM_OBJECT_NUM, _COMM_OBJECT_NUM);
    
    query.prepare(sql);
    query.bindValue(_DEVICE_ID, deviceID);
    query.bindValue(_COMM_OBJECT_NUM, commObjectNum);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"更新成功";
    return true;
}

bool updateInterfaceValueByDeviceIDAndInterfaceObjectNum(int deviceID, int interfaceObjectNum, int value)
{
    QSqlQuery query(projectDB);
    QString sql = QString("update %1 set %2 = %3 where %4 = %5 and %6 = %7").arg(INTERFACE_OBJECT_TABLE_NAME, 
                                                                                 VALUE, _VALUE, 
                                                                                 DEVICE_ID, _DEVICE_ID,
                                                                                 INTERFACE_OBJECT_NUM, _INTERFACE_OBJECT_NUM);
    
    query.prepare(sql);
    query.bindValue(_VALUE, value);
    query.bindValue(_DEVICE_ID, deviceID);
    query.bindValue(_INTERFACE_OBJECT_NUM, interfaceObjectNum);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"更新成功";
    return true;
}

bool updateAllTableProductIDAndDeviceID(int productID, int deviceID)
{
    QSqlQuery query(projectDB);

    //更新接口对象表产品id和设备id，注意：默认的设备id为null，所以更新设备id为null的数据即可
    QString sql = QString("update %1 set %2=%3,%4=%5 where %6 is null").arg(INTERFACE_OBJECT_TABLE_NAME, PRODUCT_ID, _PRODUCT_ID, DEVICE_ID, _DEVICE_ID, DEVICE_ID);
    query.prepare(sql);
    query.bindValue(_PRODUCT_ID, productID);
    query.bindValue(_DEVICE_ID, deviceID);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    
    //更新通讯对象表产品id和设备id
    sql.clear();
    sql = QString("update %1 set %2=%3,%4=%5 where %6 is null").arg(COMM_OBJECT_TABLE_NAME, PRODUCT_ID, _PRODUCT_ID, DEVICE_ID, _DEVICE_ID, DEVICE_ID);
    query.prepare(sql);
    query.bindValue(_PRODUCT_ID, productID);
    query.bindValue(_DEVICE_ID, deviceID);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    
    //更新规则表产品id和设备id
//    sql.clear();
//    sql = QString("update %1 set %2=%3,%4=%5 where %6 is null").arg(INTERFACE_COMM_OBJECT_RULE_TABLE_NAME, PRODUCT_ID, _PRODUCT_ID, DEVICE_ID, _DEVICE_ID, DEVICE_ID);
//    query.prepare(sql);
//    query.bindValue(_PRODUCT_ID, productID);
//    query.bindValue(_DEVICE_ID, deviceID);
//    if(!query.exec())
//    {
//        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
//        return false;
//    }
    
    //更新地址表产品id和设备id
    sql.clear();
    sql = QString("update %1 set %2=%3,%4=%5 where %6 is null").arg(ADDRESS_TABLE_NAME, PRODUCT_ID, _PRODUCT_ID, DEVICE_ID, _DEVICE_ID, DEVICE_ID);
    query.prepare(sql);
    query.bindValue(_PRODUCT_ID, productID);
    query.bindValue(_DEVICE_ID, deviceID);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    
    //更新关联表产品id和设备id
    sql.clear();
    sql = QString("update %1 set %2=%3,%4=%5 where %6 is null").arg(RELATION_TABLE_NAME, PRODUCT_ID, _PRODUCT_ID, DEVICE_ID, _DEVICE_ID, DEVICE_ID);
    query.prepare(sql);
    query.bindValue(_PRODUCT_ID, productID);
    query.bindValue(_DEVICE_ID, deviceID);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    
    return true;
}

QVariant selectBySql(QString sql)
{
    QSqlQuery query(projectDB);
    if(!query.exec(sql))
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"从数据库获取失败："<<query.lastError();
        return -1;
    }
    if(query.first())
    {
        qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"获取成功";
        return query.value(0);
    }
    return -2;
}

int selectNextBuildingID()
{
    QSqlQuery query(projectDB);
    QString sql = QString("select max(%1) from %2").arg(BUILDING_ID, BUILDING_TABLE_NAME);
    if(!query.exec(sql))
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return -1;
    }
    if(query.first())
    {
        qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"获取数据库建筑最大id成功";
        return (query.value(0).toInt() + 1);
    }
    return -2;
}

int selectBuildingIDByName(QString name)
{
    QSqlQuery query(projectDB);
    QString sql = QString("select %1 from %2 where %3 = ?").arg(BUILDING_ID, BUILDING_TABLE_NAME, NAME);
    query.prepare(sql);
    query.addBindValue(name);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return -1;
    }
    if(query.first())
    {
        qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"获取对应建筑名的建筑id成功";
        return (query.value(0).toInt());
    }
    return -2;
}

QString selectBuilgingNameByID(int buildingID)
{
    QSqlQuery query(projectDB);
    QString sql = QString("select %1 from %2 where %3 = ?").arg(NAME, BUILDING_TABLE_NAME, BUILDING_ID);
    query.prepare(sql);
    query.addBindValue(buildingID);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return NULL;
    }
    if(query.first())
    {
        qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"根据id获取建筑名成功"<<query.value(0).toString();
        return query.value(0).toString();
    }
    return NULL;
}

int selectNextDeviceID()
{
    QSqlQuery query(projectDB);
    QString sql = QString("select max(%1) from %2").arg(DEVICE_ID, DEVICE_TABLE_NAME);
    if(!query.exec(sql))
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return -1;
    }
    if(query.first())
    {
        qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"获取数据库设备最大id成功";
        return (query.value(0).toInt() + 1);
    }
    return -2;
}

int selectDeviceIDByName(QString name)
{
    QSqlQuery query(projectDB);
    QString sql = QString("select %1 from %2 where %3 = ?").arg(DEVICE_ID, DEVICE_TABLE_NAME, NAME);
    query.prepare(sql);
    query.addBindValue(name);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return -1;
    }
    if(query.first())
    {
        qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"获取设备id成功";
        return (query.value(0).toInt());
    }
    return -2;
}

QString selectDeviceNameByID(int deviceID)
{
    QSqlQuery query(projectDB);
    QString sql = QString("select %1 from %2 where %3 = ?").arg(NAME, DEVICE_TABLE_NAME, DEVICE_ID);
    query.prepare(sql);
    query.addBindValue(deviceID);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return NULL;
    }
    if(query.first())
    {
        qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"根据id获取设备名成功"<<query.value(0).toString();
        return query.value(0).toString();
    }
    return NULL;
}

int selectNextAddressID()
{
    QSqlQuery query(projectDB);
    QString sql = QString("select max(%1) from %2").arg(ADDRESS_ID, ADDRESS_TABLE_NAME);
    if(!query.exec(sql))
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return -1;
    }
    if(query.first())
    {
        qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"获取数据库地址最大id成功";
        return (query.value(0).toInt() + 1);
    }
    return -2;
}

int selectIDByAddressAndType(QString address, int type)
{
    QSqlQuery query(projectDB);
    QString sql = QString("select %1 from %2 where %3 = ? and %4 = ?").arg(ID, ADDRESS_TABLE_NAME, ADDRESS, TYPE);
    query.prepare(sql);
    query.addBindValue(address);
    query.addBindValue(type);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return -1;
    }
    if(query.first())
    {
        qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"获取群组地址唯一行id成功";
        return query.value(0).toInt();
    }
    return 0;
}

int isDeviceNameExist(QString deviceName)
{
    QSqlQuery query(projectDB);
    QString sql = QString("select %1 from %2").arg(NAME, DEVICE_TABLE_NAME);
    query.prepare(sql);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return -1;
    }
    while(query.next())
    {
        if(query.value(0).toString() == deviceName)
        {
            qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"设备名已存在:"<<query.value(0).toString();
            return 1;
        }
    }
    return 0;
}

int isAddressValueExist(int value, int type)
{
    QSqlQuery query(projectDB);
    QString sql = QString("select %1 from %2 where %3 = ?").arg(VALUE, ADDRESS_TABLE_NAME, TYPE);
    query.prepare(sql);
    query.addBindValue(type);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return -1;
    }
    while(query.next())
    {
        if(query.value(0).toInt() == value)
        {
            qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"value已存在："<<query.value(0).toInt()<<"type值："<<type;
            return 1;
        }
    }
    return 0;
}

int selectNextDeviceAddressIDByDeviceID(int deviceID)
{
    QSqlQuery query(projectDB);
    QString sql = QString("select max(%1) from %2 where %3 = ?").arg(ADDRESS_ID, ADDRESS_TABLE_NAME, DEVICE_ID);
    query.prepare(sql);
    query.addBindValue(deviceID);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return -1;
    }
    if(query.first())
    {
        return (query.value(0).toInt() + 1);
    }
    return -2;
}

int selectNextGroupEepromAddressByDeviceID(int deviceID)
{
    QSqlQuery query(projectDB);
    QString sql = QString("select max(%1) from %2 where %3 = ?").arg(EEPROM_ADDRESS, ADDRESS_TABLE_NAME, DEVICE_ID);
    query.prepare(sql);
    query.addBindValue(deviceID);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return -1;
    }
    if(query.first())
    {
        return (query.value(0).toInt() + 2);
    }
    return -2;
}

int selectNextAddressNumByDeviceID(int deviceID)
{
    QSqlQuery query(projectDB);
    QString sql = QString("select max(%1) from %2 where %3 = ?").arg(ADDRESS_NUM, ADDRESS_TABLE_NAME, DEVICE_ID);
    query.prepare(sql);
    query.addBindValue(deviceID);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return -1;
    }
    if(query.first())
    {
        return (query.value(0).toInt() + 1);
    }
    return -2;
}

QString selectAddressNameByDeviceIDAndGA(int deviceID, QString GA)
{
    QSqlQuery query(projectDB);
    QString sql = QString("select %1 from %2 where %3 = ? and %4 = ?").arg(NAME, ADDRESS_TABLE_NAME, DEVICE_ID, ADDRESS);
    query.prepare(sql);
    query.addBindValue(deviceID);
    query.addBindValue(GA);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return NULL;
    }
    if(query.first())
    {
        return query.value(0).toString();
    }
    return NULL;
}

int selectNextRelationIDByDeviceID(int deviceID)
{
    QSqlQuery query(projectDB);
    QString sql = QString("select max(%1) from %2 where %3 = ?").arg(RELATION_ID, RELATION_TABLE_NAME, DEVICE_ID);
    query.prepare(sql);
    query.addBindValue(deviceID);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return -1;
    }
    if(query.first())
    {
        return (query.value(0).toInt() + 1);
    }
    return -2;
}

int selectNextRelationNumByDeviceID(int deviceID)
{
    QSqlQuery query(projectDB);
    QString sql = QString("select max(%1) from %2 where %3 = ?").arg(RELATION_NUM, RELATION_TABLE_NAME, DEVICE_ID);
    query.prepare(sql);
    query.addBindValue(deviceID);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return -1;
    }
    if(query.first())
    {
        return (query.value(0).toInt() + 1);
    }
    return -2;
}

int selectNextEepromAddressByDeviceID(int deviceID)
{
    QSqlQuery query(projectDB);
    QString sql = QString("select max(%1),%2 from %3 where %4 = ?").arg(EEPROM_ADDRESS, RELATION_NUM, RELATION_TABLE_NAME, DEVICE_ID);
    query.prepare(sql);
    query.addBindValue(deviceID);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return -1;
    }
    if(query.first())
    {
        if(query.value(1).toInt() == -1)
            return (query.value(0).toInt() + 1);
        else
            return query.value(0).toInt() + 2;
    }
    return -2;
}

int selectDeviceIDByPA(QString PA)
{
    QSqlQuery query(projectDB);
    QString sql = QString("select %1 from %2 where %3 = ?").arg(DEVICE_ID, DEVICE_TABLE_NAME, DEVICE_PA);
    query.prepare(sql);
    query.addBindValue(PA);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return -1;
    }
    if(query.first())
    {
        return query.value(0).toInt();
    }
    return -2;
}

int selectInterfaceObjectDataLenByDeviceID(int deviceID)
{
    QSqlQuery query(projectDB);
    QString sql = QString("select count(*) from %1 where %2 = ? and %3 >= 0").arg(INTERFACE_OBJECT_TABLE_NAME, DEVICE_ID, INTERFACE_OBJECT_NUM);
    query.prepare(sql);
    query.addBindValue(deviceID);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return -1;
    }
    if(query.first())
    {
        return query.value(0).toInt();
    }
    return -2;
}

int selectCommObjectDataLenByDeviceID(int deviceID)
{
    QSqlQuery query(projectDB);
    QString sql = QString("select count(*) from %1 where %2 = ? and %3 >= 0").arg(COMM_OBJECT_TABLE_NAME, DEVICE_ID, COMM_OBJECT_NUM);
    query.prepare(sql);
    query.addBindValue(deviceID);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return -1;
    }
    if(query.first())
    {
        //通讯对象表除了存储通讯对象数量的第一行（占一个字节），其它每一行都包含两个数据（配置字节和数据类型各占一个字节）
        return (query.value(0).toInt() * 2 + 1);
    }
    return -2;
}

int selectAddressDataLenByDeviceID(int deviceID)
{
    QSqlQuery query(projectDB);
    QString sql = QString("select count(*) from %1 where %2 = ? and %3 >= 0").arg(ADDRESS_TABLE_NAME, DEVICE_ID, ADDRESS_NUM);
    query.prepare(sql);
    query.addBindValue(deviceID);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return -1;
    }
    if(query.first())
    {
        return (query.value(0).toInt() * 2 + 1);  //地址表除了存储地址数量的第一行，每个地址占用两个字节
    }
    return -2;
}

int selectRelationDataLenByDeviceID(int deviceID)
{
    QSqlQuery query(projectDB);
    QString sql = QString("select count(*) from %1 where %2 = ? and %3 >= 0").arg(RELATION_TABLE_NAME, DEVICE_ID, RELATION_NUM);
    query.prepare(sql);
    query.addBindValue(deviceID);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return -1;
    }
    if(query.first())
    {
        return (query.value(0).toInt() * 2 + 1);  //关联表除了存储关联数量的第一行，其它每行包含通讯对象序号和组地址序号两个字节
    }
    return -2;
}

int selectInterfaceObjectTableEepromAddressBegin(int deviceID)
{
    QSqlQuery query(projectDB);
    QString sql = QString("select min(%1) from %2 where %3 = ?").arg(EEPROM_ADDRESS, INTERFACE_OBJECT_TABLE_NAME, DEVICE_ID);
    query.prepare(sql);
    query.addBindValue(deviceID);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return -1;
    }
    if(query.first())
    {
        return query.value(0).toInt();
    }
    return -2;
}

int selectCommObjectTableEepromAddressBegin(int deviceID)
{
    QSqlQuery query(projectDB);
    QString sql = QString("select min(%1) from %2 where %3 = ?").arg(EEPROM_ADDRESS, COMM_OBJECT_TABLE_NAME, DEVICE_ID);
    query.prepare(sql);
    query.addBindValue(deviceID);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return -1;
    }
    if(query.first())
    {
        return query.value(0).toInt();
    }
    return -2;
}

int selectAddressTableEepromAddressBegin(int deviceID)
{
    QSqlQuery query(projectDB);
    QString sql = QString("select min(%1) from %2 where %3 = ?").arg(EEPROM_ADDRESS, ADDRESS_TABLE_NAME, DEVICE_ID);
    query.prepare(sql);
    query.addBindValue(deviceID);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return -1;
    }
    if(query.first())
    {
        return query.value(0).toInt();
    }
    return -2;
}

int selectRelationTableEepromAddressBegin(int deviceID)
{
    QSqlQuery query(projectDB);
    QString sql = QString("select min(%1) from %2 where %3 = ?").arg(EEPROM_ADDRESS, RELATION_TABLE_NAME, DEVICE_ID);
    query.prepare(sql);
    query.addBindValue(deviceID);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return -1;
    }
    if(query.first())
    {
        return query.value(0).toInt();
    }
    return -2;
}

uchar *selectInterfaceObjectDataByDeviceID(int deviceID, uchar *interfaceObjectTableData)
{
    QSqlQuery query(projectDB);
    QString sql = QString("select %1 from %2 where %3 = ?").arg(VALUE, INTERFACE_OBJECT_TABLE_NAME, DEVICE_ID);
    query.prepare(sql);
    query.addBindValue(deviceID);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return NULL;
    }
    int i = 0;
    while(query.next())
    {
        interfaceObjectTableData[i] = (uchar)query.value(0).toInt();
        i++;
        if(i > 255)
        {
            qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"超过存储范围";
            return NULL;
        }
    }
    return interfaceObjectTableData;
}

uchar *selectCommObjectDataByDeviceID(int deviceID, uchar *commObjectTableData)
{
    QSqlQuery query(projectDB);
    int i = 0;
    
    //先获取通讯对象数量
    QString sql = QString("select count(*) from %1 where %2 = ? and %3>=0").arg(COMM_OBJECT_TABLE_NAME, DEVICE_ID, COMM_OBJECT_NUM);
    query.prepare(sql);
    query.addBindValue(deviceID);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return NULL;
    }
    if(query.first())
    {
        commObjectTableData[i] = (uchar)(query.value(0).toInt());
        i++;
    }
    
    //然后获取并计算出配置字节和数据类型
    sql.clear();
    sql = QString("select %1,%2,%3,%4,%5,%6,%7,%8 from %9 where %10 = ? and %11 >= 0").arg(C_BIT).arg(R_BIT).arg(W_BIT).arg(V_BIT).arg(T_BIT).arg(U_BIT)
            .arg(DATA_TYPE).arg(PRIORITY).arg(COMM_OBJECT_TABLE_NAME).arg(DEVICE_ID).arg(COMM_OBJECT_NUM);
    query.prepare(sql);
    query.addBindValue(deviceID);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return NULL;
    }
    while(query.next())
    {
        //计算配置字节
        commObjectTableData[i] = (uchar)(query.value(7).toInt() + (query.value(0).toInt()<<2) + (query.value(1).toInt()<<3) + (query.value(2).toInt()<<4) +
                                         (query.value(3).toInt()<<5) + (query.value(4).toInt()<<6) + (query.value(5).toInt()<<7));
        i++;
        commObjectTableData[i] = (uchar)(query.value(6).toInt());
        i++;
        if(i > 255)
        {
            qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"超过存储范围";
            return NULL;
        }
    }
    return commObjectTableData;
}

uchar *selectAddressDataByDeviceID(int deviceID, uchar *addressTableData)
{
    QSqlQuery query(projectDB);
    int i = 0;
    
    //先获取组地址数量
    QString sql = QString("select count(*) from %1 where %2 = ? and %3>=0").arg(ADDRESS_TABLE_NAME, DEVICE_ID, ADDRESS_NUM);
    query.prepare(sql);
    query.addBindValue(deviceID);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return NULL;
    }
    if(query.first())
    {
        addressTableData[i] = (uchar)query.value(0).toInt();
        i++;
    }
    
    //然后获取组地址
    sql.clear();
    sql = QString("select %1 from %2 where %3 = ? and %4 >= 0").arg(ADDRESS).arg(ADDRESS_TABLE_NAME).arg(DEVICE_ID).arg(ADDRESS_NUM);
    query.prepare(sql);
    query.addBindValue(deviceID);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return NULL;
    }
    while(query.next())
    {
        QString address = query.value(0).toString();
        int mainGA = address.section('/', 0, 0).toInt();
        int middleGA = address.section('/', 1, 1).toInt();
        int lastGA = address.section('/', 2, 2).toInt();
        uchar GAByteHigh = (uchar)(mainGA *16 + middleGA);
        qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"组地址："<<address<<mainGA<<middleGA<<lastGA<<GAByteHigh<<lastGA;
        addressTableData[i] = GAByteHigh;
        i++;
        addressTableData[i] = (uchar)(lastGA);
        i++;
        if(i > 255)
        {
            qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"超过存储范围";
            return NULL;
        }
    }
    return addressTableData;
}

uchar *selectRelationDataByDeviceID(int deviceID, uchar *relationTableData)
{
    QSqlQuery query(projectDB);
    int i = 0;
    
    //先获取关联数量
    QString sql = QString("select count(*) from %1 where %2 = ? and %3>=0").arg(RELATION_TABLE_NAME, DEVICE_ID, RELATION_NUM);
    query.prepare(sql);
    query.addBindValue(deviceID);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return NULL;
    }
    if(query.first())
    {
        relationTableData[i] = (uchar)query.value(0).toInt();
        i++;
    }
    
    //然后获取关联的组地址序号和通讯对象序号
    sql.clear();
    sql = QString("select %1,%2 from %3 where %4 = ? and %5 >= 0").arg(ADDRESS_NUM).arg(COMM_OBJECT_NUM).arg(RELATION_TABLE_NAME).arg(DEVICE_ID).arg(RELATION_NUM);
    query.prepare(sql);
    query.addBindValue(deviceID);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return NULL;
    }
    while(query.next())
    {
        relationTableData[i] = (uchar)(query.value(0).toInt());
        i++;
        relationTableData[i] = (uchar)(query.value(1).toInt());
        i++;
        if(i > 255)
        {
            qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"超过存储范围";
            return NULL;
        }
    }
    return relationTableData;
}

int selectAddressIDByAddressAndTypeAndDeviceID(QString address, int type, int deviceID)
{
    QSqlQuery query(projectDB);
    QString sql = QString("select %1 from %2 where %3 = ? and %4 = ? and %5 = ?").arg(ADDRESS_ID, ADDRESS, ADDRESS, TYPE, DEVICE_ID);
    query.prepare(sql);
    query.addBindValue(address);
    query.addBindValue(type);
    query.addBindValue(deviceID);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return -1;
    }
    if(query.first())
    {
        return query.value(0).toInt();
    }
    return -2;
}

int selectAddressNumByAddressAndDeviceID(QString address, int deviceID)
{
    QSqlQuery query(projectDB);
    QString sql = QString("select %1 from %2 where %3 = ? and %4 = ?").arg(ADDRESS_NUM, ADDRESS_TABLE_NAME, ADDRESS, DEVICE_ID);
    query.prepare(sql);
    query.addBindValue(address);
    query.addBindValue(deviceID);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return -1;
    }
    if(query.first())
    {
        return query.value(0).toInt();
    }
    return -2;
}

int selectGroupEepromAddressByAddressAndDeviceID(QString address, int deviceID)
{
    QSqlQuery query(projectDB);
    QString sql = QString("select %1 from %2 where %3 = ? and %4 = ?").arg(EEPROM_ADDRESS, ADDRESS_TABLE_NAME, ADDRESS, DEVICE_ID);
    query.prepare(sql);
    query.addBindValue(address);
    query.addBindValue(deviceID);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return -1;
    }
    if(query.first())
    {
        return query.value(0).toInt();
    }
    return -2;
}

int selectRelationIDByDeviceIDAndCommObjectNumAndAddressNum(int deviceID, int commObjectNum, int addressNum)
{
    QSqlQuery query(projectDB);
    QString sql = QString("select %1 from %2 where %3 = ? and %4 = ? and %5 = ?").arg(RELATION_ID, 
                                                                                      RELATION_TABLE_NAME, DEVICE_ID, 
                                                                                      COMM_OBJECT_NUM, ADDRESS_NUM);
    if(!query.prepare(sql))
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return -1;
    }
    query.addBindValue(deviceID);
    query.addBindValue(commObjectNum);
    query.addBindValue(addressNum);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return -2;
    }
    if(query.first())
    {
        return query.value(0).toInt();
    }
    return -3;
}

int selectRelationNumByDeviceIDAndCommObjectNumAndAddressNum(int deviceID, int commObjectNum, int addressNum)
{
    QSqlQuery query(projectDB);
    QString sql = QString("select %1 from %2 where %3 = ? and %4 = ? and %5 = ?").arg(RELATION_NUM, 
                                                                                      RELATION_TABLE_NAME, DEVICE_ID, 
                                                                                      COMM_OBJECT_NUM, ADDRESS_NUM);
    query.prepare(sql);
    query.addBindValue(deviceID);
    query.addBindValue(commObjectNum);
    query.addBindValue(addressNum);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return -1;
    }
    if(query.first())
    {
        return query.value(0).toInt();
    }
    return -2;
}

int selectRelationEepromAddressByDeviceIDAndCommObjectNumAndAddressNum(int deviceID, int commObjectNum, int addressNum)
{
    QSqlQuery query(projectDB);
    QString sql = QString("select %1 from %2 where %3 = ? and %4 = ? and %5 = ?").arg(EEPROM_ADDRESS, 
                                                                                      RELATION_TABLE_NAME, DEVICE_ID, 
                                                                                      COMM_OBJECT_NUM, ADDRESS_NUM);
    query.prepare(sql);
    query.addBindValue(deviceID);
    query.addBindValue(commObjectNum);
    query.addBindValue(addressNum);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return -1;
    }
    if(query.first())
    {
        return query.value(0).toInt();
    }
    return -2;
}

int selectInterfaceObjectValueByDeviceIDAndInterfaceObjectNum(int deviceID, int interfaceObjectNum)
{
    QSqlQuery query(projectDB);
    QString sql = QString("select %1 from %2 where %3 = ? and %4 = ?").arg(VALUE, INTERFACE_OBJECT_TABLE_NAME, 
                                                                           DEVICE_ID, INTERFACE_OBJECT_NUM);
    query.prepare(sql);
    query.addBindValue(deviceID);
    query.addBindValue(interfaceObjectNum);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return -1;
    }
    if(query.first())
    {
        return query.value(0).toInt();
    }
    return -2;
}
